package gmusic.ma.dao.impl;

import gmusic.ma.bo.ArtistMABean;
import gmusic.ma.bo.ReleaseMABean;
import gmusic.ma.bo.TrackMABean;
import gmusic.ma.dao.MetalArchivesDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import algutil.connexion.GestionConnexion;

public class MetalArchivesDAOImpl implements MetalArchivesDAO {

	private static final Logger log = Logger
			.getLogger(MetalArchivesDAOImpl.class);

	private static MetalArchivesDAO instance;

	public static MetalArchivesDAO getInstance() {

		if (instance == null)
			instance = new MetalArchivesDAOImpl();
		return instance;
	}

	/** @deprecated */
	public void ajouter(int id, String nom) throws SQLException {

		// Connection c = GestionConnexion.getInstance().getConnexion();
		// PreparedStatement p = c.prepareStatement("INSERT INTO ma_groupe (id,
		// nom, creation_date, last_update_date) VALUES (?, ?, now(), now())");
		// p.setInt(1, id);
		// p.setString(2, nom);
		// p.executeUpdate();
		// p.close();
		// c.close();
	}

	/** @deprecated */
	public List<ArtistMABean> getMAGroupesCommencantPar(String debut)
			throws SQLException {

		List<ArtistMABean> groupes = new ArrayList<ArtistMABean>();

		// Connection c = GestionConnexion.getInstance().getConnexion();
		// PreparedStatement p = c.prepareStatement("SELECT * FROM ma_groupe
		// WHERE nom LIKE '"+debut+"%' ORDER BY nom");
		// //p.setString(1, debut);
		// ResultSet r = p.executeQuery();
		//
		// while (r.next()) {
		// groupes.add(new MAArtisteBean(r.getInt("id"), r.getString("nom")));
		// }

		return groupes;
	}

	/** @deprecated */
	public List<String> getPremieresLettres() throws SQLException {

		List<String> premieresLettres = new ArrayList<String>();

		// Connection c = GestionConnexion.getInstance().getConnexion();
		// PreparedStatement p = c.prepareStatement("SELECT DISTINCT substr(
		// nom, 1, 1 ) FROM ma_groupe");
		// ResultSet r = p.executeQuery();
		//
		// while (r.next()) {
		// premieresLettres.add(r.getString(1));
		// }

		return premieresLettres;

	}

	public boolean ajouter(ArtistMABean ab) throws SQLException {

		// Ajout de l'artiste en BD
		if (!artisteMAExists(ab.getMaid())) {
			Connection c = GestionConnexion.getConnexionGMusic();
			log.info("Insersion en BD de l'artiste " + ab);
			PreparedStatement p = c
					.prepareStatement("INSERT INTO ma_artist (maid, nom, pays_id, url_logo, creation_date, last_update_date) VALUES (?, ?, ?, ?, now(), now())");
			p.setLong(1, ab.getMaid());
			p.setString(2, ab.getNom());
			p.setInt(3, ab.getPaysMaid());
			p.setString(4, ab.getURLLogo());
			p.executeUpdate();
			p.close();
			return true;

		} else {
			log.info("L'artiste existe deja en BD : " + ab);
			return false;
		}
	}

	public boolean ajouter(ReleaseMABean rb) throws SQLException {

		// Ajout des releases en BD
		if (!releaseMAExists(rb.getMaid())) {
			Connection c = GestionConnexion.getConnexionGMusic();
			log.info("Insertion en BD de la release " + rb);
			PreparedStatement p = c
					.prepareStatement("INSERT INTO ma_release (maid, nom, groupe_maid, annee, type_id, url_cover, creation_date, last_update_date) VALUES (?, ?, ?, ?, ?, ?, now(), now())");
			p.setLong(1, rb.getMaid());
			p.setString(2, rb.getNom());
			p.setLong(3, rb.getArtistMaId());
			p.setInt(4, rb.getAnnee());
			p.setInt(5, rb.getTypeId());
			p.setString(6, rb.getURLCover());
			p.executeUpdate();

			p.close();
			return true;
		} else {
			log.info("La release existe deja en BD : " + rb);
			return false;
		}
	}

	public boolean ajouter(TrackMABean tb) throws SQLException {

		if (!trackMAExists(tb)) {
			Connection c = GestionConnexion.getConnexionGMusic();
			log.info("Insertion en BD de la chanson " + tb);
			PreparedStatement p = c
					.prepareStatement("INSERT INTO ma_track (maid, release_maid, disc, num, nom, duree, lyric, creation_date) VALUES (?, ?, ?, ?, ?, ?, ?, now())");
			p.setLong(1, tb.getMaid());
			p.setLong(2, tb.getReleaseMaid());
			p.setInt(3, tb.getDisc());
			p.setInt(4, tb.getNumero());
			p.setString(5, tb.getNom());
			p.setInt(6, tb.getDuree());
			p.setString(7, tb.getLyrics());
			p.executeUpdate();
			p.close();
			return true;
		} else {
			log.info("La chanson existe deja en BD : " + tb);
			return false;
		}
	}

	public int getPaysMaid(String nomPays) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_pays WHERE nom=?");
		p.setString(1, nomPays);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			int paysId = r.getInt(1);
			log.debug("L'id du pays '" + nomPays + "' : " + paysId);
			return paysId;
		} else {
			log.error("Erreur - L'id du pays '" + nomPays
					+ "' n'a pas ete trouve!");
			return -1;
		}
	}

	public int getMAReleaseTypeId(String nomType) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT ty_id FROM type WHERE ty_nom=?");
		p.setString(1, nomType);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			int typeId = r.getInt(1);
			log.debug("L'id du type '" + nomType + "' : " + typeId);
			return typeId;
		} else {
			log.error("Erreur - L'id du type '" + nomType
					+ "' n'a pas ete trouve!");
			return -1;
		}
	}

	public boolean artisteMAExists(long maid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_artist WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			return true;
		} else {
			return false;
		}
	}

	public boolean releaseMAExists(long maid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_release WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			return true;
		} else {
			return false;
		}
	}

	public boolean trackMAExists(TrackMABean tb) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_track WHERE release_maid=? and disc=? and num=?");
		p.setLong(1, tb.getReleaseMaid());
		p.setInt(2, tb.getDisc());
		p.setInt(3, tb.getNumero());
		ResultSet r = p.executeQuery();
		if (r.next()) {
			return true;
		} else {
			return false;
		}
	}

	/** @deprecated */
	public boolean trackMAExists(long maid) throws SQLException {
		try {
			new Long(maid);
		} catch (Exception e) {
			// maid null
			return false;
		}
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT maid FROM ma_track WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			return true;
		} else {
			return false;
		}
	}

	public List<ArtistMABean> rechercherArtistes(String nom)
			throws SQLException {
		List<ArtistMABean> artistes = new ArrayList<ArtistMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMABean(r));
		}

		return artistes;
	}

	public ReleaseMABean getRelease(long release_maid) throws SQLException {
		ReleaseMABean rb = null;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE maid=?");
		p.setLong(1, release_maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			rb = new ReleaseMABean(r);
		} else {
			log.error("La release n'a pas ete trouve " + release_maid);
		}

		return rb;
	}

	public ReleaseMABean getMAReleaseAndTracks(long release_maid)
			throws SQLException {

		ReleaseMABean rb = getRelease(release_maid);
		if (rb != null) {
			rb.setTracksMA(getTracks(release_maid));
		}

		return rb;
	}

	public List<TrackMABean> getTracks(long release_maid) throws SQLException {
		List<TrackMABean> tracks = new ArrayList<TrackMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_track WHERE release_maid=?");
		p.setLong(1, release_maid);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			tracks.add(new TrackMABean(r));
		}

		if (tracks.size() == 0) {
			log.warn("La release n'a pas de tracks " + release_maid);
		}

		return tracks;
	}

	public List<ReleaseMABean> getMAReleases(long artist_maid)
			throws SQLException {
		List<ReleaseMABean> releases = new ArrayList<ReleaseMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE groupe_maid=?");
		p.setLong(1, artist_maid);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMABean(r));
		}

		return releases;
	}

	public List<ReleaseMABean> getMAReleasesAndTracks(long artist_maid)
			throws SQLException {
		List<ReleaseMABean> releases = getMAReleases(artist_maid);
		for (int i = 0; i < releases.size(); i++) {
			releases.get(i).setTracksMA(getTracks(releases.get(i).getMaid()));
		}
		return releases;
	}

	public List<ArtistMABean> getAllArtists() throws SQLException {
		List<ArtistMABean> artistes = new ArrayList<ArtistMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist ORDER BY nom");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMABean(r));
		}

		return artistes;
	}

	public ArtistMABean getArtist(long maid) throws SQLException {
		ArtistMABean artiste = null;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_artist WHERE maid=?");
		p.setLong(1, maid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			artiste = new ArtistMABean(r);
		}

		return artiste;
	}

	public List<ReleaseMABean> rechercherReleases(String nom)
			throws SQLException {
		List<ReleaseMABean> releases = new ArrayList<ReleaseMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_release WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMABean(r));
		}

		return releases;
	}

	public List<TrackMABean> rechercherTracks(String nom) throws SQLException {
		List<TrackMABean> tracks = new ArrayList<TrackMABean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM ma_track WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			tracks.add(new TrackMABean(r));
		}

		return tracks;
	}

	public int getPaysGmid(int paysMaid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT pa_id FROM pays WHERE maid=?");
		p.setInt(1, paysMaid);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			return r.getInt(1);
		} else {
			return -1;
		}
	}

	public int getNbDisc(long releaseMaid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT COUNT(DISTINCT disc) FROM ma_track WHERE release_maid=?");
		p.setLong(1, releaseMaid);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			return r.getInt(1);
		} else {
			return -1;
		}
	}

	public int getNbTracks(long releaseMaid, int discNum) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT COUNT(*) FROM ma_track WHERE release_maid=? AND disc=?");
		p.setLong(1, releaseMaid);
		p.setInt(1, discNum);
		ResultSet r = p.executeQuery();

		if (r.next()) {
			return r.getInt(1);
		} else {
			return -1;
		}
	}
}
